﻿#include "pagefile.h"
#include <boost/format.hpp>
#include <boost/format.hpp>
#include <boost/lexical_cast.hpp>  
#include "../../liblog/Include/liblog.h"

const char* db_name = "record.db";

CPagefile::CPagefile()
: pagefile_db(NULL)
{

}

CPagefile::~CPagefile()
{

}

CPagefile& CPagefile::GetInstance()
{
	static CPagefile pagefile;
	return pagefile;
}

inline int sqlite3_affect_exec(sqlite3* db, const char *sql,char **errmsg)
{
	return sqlite3_exec(db, sql, NULL, NULL, errmsg);
}

int CPagefile::Start()
{
    boost::recursive_mutex::scoped_lock lock(_rmutex);
	int ret = sqlite3_open_v2(db_name, &pagefile_db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX, NULL);
	if (ret != SQLITE_OK)
	{
		LogError(("%s failed.error:%s.\r\n", __PRETTY_FUNCTION__, sqlite3_errstr(ret)));
		return -1;
	}
    
    //begin:add creat table device_info by ypj, at 2017/6/19
	char* msg = NULL;
	ret = sqlite3_affect_exec(pagefile_db,
		"CREATE TABLE IF NOT EXISTS DEVICE_INFO\
		 (DEVICE_ID VARCHAR (20) PRIMARY KEY,HARDWARE_ID TEXT,DEVICE_KEY VARCHAR(24),SNED_SN INT);",&msg);
	if (ret != SQLITE_OK)
	{
		LogError(("%s failed.code:%d error:%s.\r\n", __PRETTY_FUNCTION__, ret, msg));
		sqlite3_free(msg);
		sqlite3_close_v2(pagefile_db);
		pagefile_db = NULL;
		return -1;
	}
    //end
	return 0;
}

int CPagefile::Stop()
{
    boost::recursive_mutex::scoped_lock lock(_rmutex);
	sqlite3_close_v2(pagefile_db);
	pagefile_db = NULL;
	return 0;
}


bool CPagefile::GetNewDeviceInfo(const std::string hardwareid, std::string &deviceid, std::string &devicekey)
{
    if (!pagefile_db)
        return false;

    //sqlite3_prepare_v2
    boost::recursive_mutex::scoped_lock lock(_rmutex);
	const char* select_sql = "SELECT DEVICE_ID, DEVICE_KEY FROM DEVICE_INFO WHERE HARDWARE_ID = \'%s\';";
 	std::string sql = boost::str(boost::format(select_sql) % hardwareid);
    
	int nRow = 0, nColumn = 0;
	char* msg = NULL, **resultSet = NULL;
	int ret = sqlite3_get_table(pagefile_db, sql.c_str(), &resultSet, &nRow, &nColumn, &msg);
	if (ret != SQLITE_OK)
	{
		LogError(("%s:sql %s, failed.code:%d error:%s.\r\n", __PRETTY_FUNCTION__, sql.c_str(), ret, msg));
		sqlite3_free(msg);
		return false;
	}
    std::string strdeviceid = "";
    std::string strdevicekey = "";
    if (nRow > 0)
    {
        strdeviceid = std::string(resultSet[1 + 1]);
        strdevicekey = std::string(resultSet[1 + 2]);
        sqlite3_free_table(resultSet);
    }
    else
    {
        const char* select_sql1 = "SELECT DEVICE_ID, DEVICE_KEY FROM DEVICE_INFO WHERE HARDWARE_ID = \"\" OR HARDWARE_ID IS NULL;";
        ret = sqlite3_get_table(pagefile_db, select_sql1, &resultSet, &nRow, &nColumn, &msg);
        if (ret != SQLITE_OK)
        {
            LogError(("%s sql:&s failed.code:%d error:%s.\r\n", __PRETTY_FUNCTION__, select_sql1, ret, msg));
            sqlite3_free(msg);
            return false;
        }
        
        if (nRow > 0)
        {
            strdeviceid = std::string(resultSet[1 + 1]);
            strdevicekey = std::string(resultSet[1 + 2]);
            const char* update_sql =
                "UPDATE DEVICE_INFO SET HARDWARE_ID = \'%s\' WHERE DEVICE_ID=\'%s\';";
            
            std::string sql = boost::str(boost::format(update_sql) % hardwareid % strdeviceid);
            sqlite3_free_table(resultSet);
            int ret = sqlite3_affect_exec(pagefile_db, sql.c_str(),&msg);
            if (ret != SQLITE_OK)
            {
                LogError(("%s:sql %s failed.code:%d error:%s.\r\n", __PRETTY_FUNCTION__, sql.c_str(), ret, msg));
                sqlite3_free(msg);
                return false;
            }
        }
        else
        {
            LogError(("%s:设备ID已用完,请申请新的设备ID\n", hardwareid.c_str()));
            return false;
        }
    }
    deviceid = strdeviceid;
    devicekey = strdevicekey;
    return true;
}


int CPagefile::GetSendSnByHardid(const std::string deviceid)
{
	const char* select_sql = "SELECT SEND_SN FROM DEVICE_INFO WHERE DEVICE_ID = \'%s\';";
 	std::string sql = boost::str(boost::format(select_sql) % deviceid);
	int nRow = 0, nColumn = 0;
    char* msg = NULL, **resultSet = NULL;
    boost::recursive_mutex::scoped_lock lock(_rmutex);
	int ret = sqlite3_get_table(pagefile_db, sql.c_str(), &resultSet, &nRow, &nColumn, &msg);
	if (ret != SQLITE_OK)
	{
		LogError(("%s:sql %s, failed.code:%d error:%s.\r\n", __PRETTY_FUNCTION__, sql.c_str(), ret, msg));
		sqlite3_free(msg);
		return -1;
	}
    int sn = boost::lexical_cast<int>(resultSet[1]);
    sn++;
	sqlite3_free_table(resultSet);

    
    const char* update_sql =
        "UPDATE DEVICE_INFO SET SEND_SN = %d WHERE DEVICE_ID=\'%s\';";
    sql = boost::str(boost::format(update_sql) % sn % deviceid);
    ret = sqlite3_affect_exec(pagefile_db, sql.c_str(),&msg);
    if (ret != SQLITE_OK)
    {
        LogError(("%s:sql %s failed.code:%d error:%s.\r\n", __PRETTY_FUNCTION__, sql.c_str(), ret, msg));
        sqlite3_free(msg);
        return -1;
    }

    return sn;
}


int CPagefile::GetDeviceInfoByHid(const std::string hardwareid, std::string &deviceid, std::string &devicekey)
{
    boost::recursive_mutex::scoped_lock lock(_rmutex);
	const char* select_sql = "SELECT DEVICE_ID, DEVICE_KEY FROM DEVICE_INFO WHERE HARDWARE_ID = \'%s\';";
 	std::string sql = boost::str(boost::format(select_sql) % hardwareid);
    
	int nRow = 0, nColumn = 0;
	char* msg = NULL, **resultSet = NULL;
	int ret = sqlite3_get_table(pagefile_db, sql.c_str(), &resultSet, &nRow, &nColumn, &msg);
	if (ret != SQLITE_OK)
	{
		LogError(("%s:sql %s, failed.code:%d error:%s.\r\n", __PRETTY_FUNCTION__, sql.c_str(), ret, msg));
		sqlite3_free(msg);
		return -1;
    }
    std::string strdeviceid = "";
    std::string strdevicekey = "";
    if (nRow > 0)
    {
        strdeviceid = std::string(resultSet[1 + 1]);
        strdevicekey = std::string(resultSet[1 + 2]);
        sqlite3_free_table(resultSet);
    }
    deviceid = strdeviceid;
    devicekey = strdevicekey;
    return 0;
}

int CPagefile::GetDeviceInfoByDid(const std::string deviceid, std::string &hardwareid, std::string &devicekey)
{
    boost::recursive_mutex::scoped_lock lock(_rmutex);
	const char* select_sql = "SELECT HARDWARE_ID, DEVICE_KEY FROM DEVICE_INFO WHERE DEVICE_ID = \'%s\';";
 	std::string sql = boost::str(boost::format(select_sql) % deviceid);
    
	int nRow = 0, nColumn = 0;
	char* msg = NULL, **resultSet = NULL;
	int ret = sqlite3_get_table(pagefile_db, sql.c_str(), &resultSet, &nRow, &nColumn, &msg);
	if (ret != SQLITE_OK)
	{
		LogError(("%s:sql %s, failed.code:%d error:%s.\r\n", __PRETTY_FUNCTION__, sql.c_str(), ret, msg));
		sqlite3_free(msg);
		return -1;
    }
    std::string strhardwareid = "";
    std::string strdevicekey = "";
    if (nRow > 0)
    {
        strhardwareid = std::string(resultSet[1 + 1]);
        strdevicekey = std::string(resultSet[1 + 2]);
        sqlite3_free_table(resultSet);
    }
    hardwareid = strhardwareid;
    devicekey = strdevicekey;
    return 0;
}

int CPagefile::GetAllDeviceInfo(std::vector<DEVINFO> &vecdevinfo)
{
    const char* sql = "SELECT DEVICE_ID, HARDWARE_ID, DEVICE_KEY FROM DEVICE_INFO";
    sqlite3_stmt *stmt = NULL;
    boost::recursive_mutex::scoped_lock lock(_rmutex);
    sqlite3_prepare_v2(pagefile_db, sql, strlen(sql), &stmt, NULL);  
    while(sqlite3_step(stmt) == SQLITE_ROW){  
        /* 获得字节数，第二个参数为select结果集合的列号 */  
        DEVINFO devinfo;
        const char* chrdid = (const char*)sqlite3_column_text(stmt, 0);
        if (chrdid == NULL)
            devinfo.strdid = "";
        else
            devinfo.strdid = std::string(chrdid);

        const char *chrhid = (const char*)sqlite3_column_text(stmt, 1);
        if (chrhid == NULL)
            devinfo.strhid = "";
        else
            devinfo.strhid = std::string(chrhid);

        const char *charkey = (const char*)sqlite3_column_text(stmt, 2);
        if (charkey == NULL)
            devinfo.strkey = "";
        else
            devinfo.strkey = std::string(charkey);

        printf("did:%s,", devinfo.strdid.c_str());  
        printf("hid:%s,", devinfo.strhid.c_str());  
        printf("key:%s\n", devinfo.strkey.c_str());  
        vecdevinfo.push_back(devinfo);
    }
    sqlite3_finalize(stmt);
    return 0;
}

int CPagefile::GetDeviceInfoOfHidIsNull(std::vector<DEVINFO> &vecdevinfo)
{
    const char* sql = "SELECT DEVICE_ID, DEVICE_KEY FROM DEVICE_INFO WHERE HARDWARE_ID = \"\" OR HARDWARE_ID IS NULL";
    sqlite3_stmt *stmt = NULL;
    boost::recursive_mutex::scoped_lock lock(_rmutex);
    sqlite3_prepare_v2(pagefile_db, sql, strlen(sql), &stmt, NULL);  
    while(sqlite3_step(stmt) == SQLITE_ROW){  
        /* 获得字节数，第二个参数为select结果集合的列号 */  
        DEVINFO devinfo;
        const char* chrdid = (const char*)sqlite3_column_text(stmt, 0);
        if (chrdid == NULL)
            devinfo.strdid = "";
        else
            devinfo.strdid = std::string(chrdid);

        devinfo.strhid = "";

        const char* chrkey = (const char*)sqlite3_column_text(stmt, 1);
        if (chrkey == NULL)
            devinfo.strkey = "";
        else
            devinfo.strkey = std::string(chrkey);
        printf("did:%s,", devinfo.strdid.c_str());  
        printf("key:%s\n", devinfo.strkey.c_str());  
        vecdevinfo.push_back(devinfo);
    }
    sqlite3_finalize(stmt);
    return 0;
}

int CPagefile::GetDeviceInfoOfHid(std::vector<DEVINFO> &vecdevinfo)
{
    const char* sql = "SELECT DEVICE_ID, HARDWARE_ID, DEVICE_KEY FROM DEVICE_INFO WHERE HARDWARE_ID IS NOT NULL AND HARDWARE_ID != \"\"";
    sqlite3_stmt *stmt = NULL;
    boost::recursive_mutex::scoped_lock lock(_rmutex);
    sqlite3_prepare_v2(pagefile_db, sql, strlen(sql), &stmt, NULL);  
    while(sqlite3_step(stmt) == SQLITE_ROW){  
        /* 获得字节数，第二个参数为select结果集合的列号 */  
        DEVINFO devinfo;
        const char* chrdid = (const char*)sqlite3_column_text(stmt, 0);
        if (chrdid == NULL)
            devinfo.strdid = "";
        else
            devinfo.strdid = std::string(chrdid);

        const char* chrhid = (const char*)sqlite3_column_text(stmt, 1);
        if (chrhid == NULL)
            devinfo.strhid = "";
        else
            devinfo.strhid = std::string(chrhid);

        const char* chrkey = (const char*)sqlite3_column_text(stmt, 2);
        if (chrkey == NULL)
            devinfo.strkey = "";
        else
            devinfo.strkey = std::string(chrkey);

        printf("did:%s,", devinfo.strdid.c_str());  
        printf("hid:%s,", devinfo.strhid.c_str());  
        printf("key:%s\n", devinfo.strkey.c_str());  
        vecdevinfo.push_back(devinfo);
    }
    sqlite3_finalize(stmt);
    return 0;
}

int CPagefile::DelDeviceInfoByDid(const std::string deviceid)
{
    std::string strsql = boost::str(boost::format("DELETE FROM DEVICE_INFO WHERE DEVICE_ID=\'%s\'")%deviceid);
   // sqlite3_stmt *stmt;
    boost::recursive_mutex::scoped_lock lock(_rmutex);
   // sqlite3_prepare_v2(pagefile_db, strsql.c_str(), strsql.length(), &stmt, NULL);  
    //sqlite3_finalize(stmt);
    char * errMsg = NULL;
    sqlite3_exec( pagefile_db , strsql.c_str() , 0 , 0 , &errMsg ); 
    LogDebug(("del:%s,err:%s\r\n", strsql.c_str(), errMsg));
    return 0;
}

int CPagefile::AddDeviceInfo(const std::string deviceid, const std::string hardwareid, const std::string devicekey)
{
    if (deviceid.length() == 0 ||  devicekey.length() == 0)
        return -1;
    
    std::string strsql = boost::str(boost::format("SELECT DEVICE_ID, HARDWARE_ID, DEVICE_KEY FROM DEVICE_INFO WHERE DEVICE_ID=\'%s\'")%deviceid);
    sqlite3_stmt *stmt;
    boost::recursive_mutex::scoped_lock lock(_rmutex);
    sqlite3_prepare_v2(pagefile_db, strsql.c_str(), strsql.length(), &stmt, NULL);  
    //int column = sqlite3_column_count(stmt);
    if (sqlite3_step(stmt) == SQLITE_ROW)//在已有did的情况下绑定硬件ID
    {
        //if(hardwareid.length() == 0)
        //    return 0; //没有可绑定的

        strsql = boost::str(boost::format("UPDATE DEVICE_INFO SET HARDWARE_ID = \'%s\' WHERE DEVICE_ID=\'%s\';")%hardwareid %deviceid);
        char * errMsg = NULL;
        sqlite3_exec( pagefile_db , strsql.c_str() , 0 , 0 , &errMsg );  
        LogDebug(("add:%s,err:%s\r\n", strsql.c_str(), errMsg));
    }
    else
    {
        if (hardwareid.length() == 0)
        {
            strsql = boost::str(boost::format("INSERT INTO DEVICE_INFO (DEVICE_ID, DEVICE_KEY) VALUES(\"%s\",\"%s\");")%deviceid %devicekey);
        }
        else
        {
            strsql = boost::str(boost::format("INSERT INTO DEVICE_INFO (DEVICE_ID, HARDWARE_ID, DEVICE_KEY) VALUES(\"%s\",\"%s\",\"%s\");")%deviceid %hardwareid %devicekey);
        }

        char * errMsg = NULL;
        sqlite3_exec( pagefile_db , strsql.c_str() , 0 , 0 , &errMsg );  
        LogDebug(("add:%s,err:%s\r\n", strsql.c_str(), errMsg));

    }
    sqlite3_finalize(stmt);
    return 0;
}

